* =======================================================================================================================================================
*
* Code Description: 
* This codefile computes the figures provided in Table 3: Outflows and trading activity. The table shows the relationship between funds' active trading
* activity in a given asset class c (evaluated at prices in t-1, as in Eq. (3)) and the magnitude of outflows during the same period.
*
* =======================================================================================================================================================
*
* Major output:
* Table 3: Outflows and trading activity.
* =======================================================================================================================================================
*
* General disclaimer:
* This file directory produces replication code for "Connected Funds". 
* Because we cannot share the underlying data provided by the Bundesbank's Research Data and Service Centre (RDSC) and other subscription data sources, 
* we have included pseudo data to show how the raw data are formatted. 
* Other researchers can go through a similar approval and subscription process to obtain the underlying data. (2023-04-06)
*
* =======================================================================================================================================================


// Paths
global Path "C:\ConnectedFunds_Codebase\"
global Codes "Codes" 
sysdir set STBPLUS "${Path}Code\Ado"   

clear all
est clear
set more off 

set scheme plottig, perm


* ============================================= Import and clean fund data ==============================================================


cd "${Path}Data\"

// Note that MainData is generated by file "Empirical_9_Compute_FlowDrivenAndDiscretionaryPurchases.m"
import delim "MainData.csv", delimiters(",") case(preserve)

// Generate portfolio change variables
gen rDeltaFundShares_VolumeEffect = (DeltaHoldings_VolumeEffect_FundS / FONDSVERM_lag) * 100
gen rDeltaStocks_VolumeEffect     = (DeltaHoldings_VolumeEffect_Equit / FONDSVERM_lag) * 100
gen rDeltaCorpBonds_VolumeEffect  = (DeltaHoldings_VolumeEffect_CorpB / FONDSVERM_lag) * 100
gen rDeltaSovBonds_VolumeEffect   = (DeltaHoldings_VolumeEffect_SovBo / FONDSVERM_lag) * 100
gen rNETFLOWS          			  = (NETFLOWS / FONDSVERM_lag) * 100

// Winsor regression variables at 1%/99%-level, by special fund dummy (0/1) 
foreach winsorized_var of varlist rDeltaFundShares_VolumeEffect rDeltaStocks_VolumeEffect rDeltaCorpBonds_VolumeEffect rDeltaSovBonds_VolumeEffect rNETFLOWS {

	gen Ws_`winsorized_var' = .

	forval spezial = 0/1 {
		winsor `winsorized_var' if SPEZIAL == `spezial', p(0.01) gen(temp)
		replace Ws_`winsorized_var' = temp if SPEZIAL == `spezial'
		drop temp
	}
}

summ r* Ws_*

gen VIX = vixclose

keep if abs(rNETFLOWS) < 80
keep if rNETFLOWS < 0
capture drop if FONDSVERM_lag == .
capture drop if FONDSVERM_lag <= 0

keep if TotalHoldings_FundShares_lag > 0

gen dum_High_VIX = 0
replace dum_High_VIX = 1 if VIX >= r(p75)


* ============================================= Construct Table 3, Panel A: retail funds ==============================================================


preserve 

keep if SPEZIAL == 0 

xtile tmp_pct = rNETFLOWS, nq(5)

egen tmp_flow = wtmean(rNETFLOWS), by(tmp_pct) weight(FONDSVERM_lag)

gen delta_cash = 100*(BANKG - BANKG_lag)/FONDSVERM_lag
winsor delta_cash, gen(Ws_delta_cash) p(0.01)

egen tmp_cash  = wtmean(Ws_delta_cash), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_fund  = wtmean(Ws_rDeltaFundShares_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_stock = wtmean(Ws_rDeltaStocks_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_corp  = wtmean(Ws_rDeltaCorpBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_sov   = wtmean(Ws_rDeltaSovBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)

keep tmp_*
duplicates drop tmp_pct, force

sort tmp_pct

// Save results as table 3, panel A
export excel using "${Path}Paper\Tables\Tab3_Outflows and trading activity.xlsx", sheet("Results", modify) cell(B3) firstrow(variables)

restore

// Same conditional on high vix periods
preserve 

keep if SPEZIAL == 0 
keep if dum_High_VIX == 1

xtile tmp_pct = rNETFLOWS, nq(5)

egen tmp_flow = wtmean(rNETFLOWS), by(tmp_pct) weight(FONDSVERM_lag)

gen delta_cash = 100*(BANKG - BANKG_lag)/FONDSVERM_lag
winsor delta_cash, gen(Ws_delta_cash) p(0.01)

egen tmp_cash  = wtmean(Ws_delta_cash), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_fund  = wtmean(Ws_rDeltaFundShares_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_stock = wtmean(Ws_rDeltaStocks_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_corp  = wtmean(Ws_rDeltaCorpBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_sov   = wtmean(Ws_rDeltaSovBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)

keep tmp_*
duplicates drop tmp_pct, force

sort tmp_pct

// Save results 
export excel using "${Path}Paper\Tables\Tab3_Outflows and trading activity.xlsx", sheet("Results_HighVIX", modify) cell(B3) firstrow(variables)

restore



* ============================================= Construct Table 3, Panel B: institutional funds ==============================================================


preserve 

keep if SPEZIAL == 1

xtile tmp_pct = rNETFLOWS, nq(5)

egen tmp_flow = wtmean(rNETFLOWS), by(tmp_pct) weight(FONDSVERM_lag)

gen delta_cash = 100*(BANKG - BANKG_lag)/FONDSVERM_lag
winsor delta_cash, gen(Ws_delta_cash) p(0.01)

egen tmp_cash  = wtmean(Ws_delta_cash), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_fund  = wtmean(Ws_rDeltaFundShares_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_stock = wtmean(Ws_rDeltaStocks_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_corp  = wtmean(Ws_rDeltaCorpBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_sov   = wtmean(Ws_rDeltaSovBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)

keep tmp_*
duplicates drop tmp_pct, force

sort tmp_pct

// Save results as table 3, panel B
export excel using "${Path}Paper\Tables\Tab3_Outflows and trading activity.xlsx", sheet("Results", modify) cell(N3) firstrow(variables)

restore


// Same conditional on high vix periods
preserve 

keep if SPEZIAL == 1
keep if dum_High_VIX == 1

xtile tmp_pct = rNETFLOWS, nq(5)

egen tmp_flow = wtmean(rNETFLOWS), by(tmp_pct) weight(FONDSVERM_lag)

gen delta_cash = 100*(BANKG - BANKG_lag)/FONDSVERM_lag
winsor delta_cash, gen(Ws_delta_cash) p(0.01)

egen tmp_cash  = wtmean(Ws_delta_cash), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_fund  = wtmean(Ws_rDeltaFundShares_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_stock = wtmean(Ws_rDeltaStocks_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_corp  = wtmean(Ws_rDeltaCorpBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)
egen tmp_sov   = wtmean(Ws_rDeltaSovBonds_VolumeEffect), by(tmp_pct) weight(FONDSVERM_lag)

keep tmp_*
duplicates drop tmp_pct, force

sort tmp_pct

// Save results
export excel using "${Path}Paper\Tables\Tab3_Outflows and trading activity.xlsx", sheet("Results_HighVIX", modify) cell(N3) firstrow(variables)

restore